Requery Method Example

This example shows how the Requery method can be used to refresh a query after underlying data has been changed.

Sub RequeryX()

    Dim dbsNorthwind As Database
    Dim qdfTemp As QueryDef
    Dim rstView As Recordset
    Dim rstChange As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set qdfTemp = dbsNorthwind.CreateQueryDef("", _
        "PARAMETERS ViewCountry Text; " & _
        "SELECT FirstName, LastName, Country FROM " & _
        "Employees WHERE Country = [ViewCountry] " & _
        "ORDER BY LastName")

    qdfTemp.Parameters!ViewCountry = "USA"
    Debug.Print "Data after initial query, " & _
        [ViewCountry] = USA"
    Set rstView = qdfTemp.OpenRecordset
    Do While Not rstView.EOF
        Debug.Print "  " & rstView!FirstName & " " & _
            rstView!LastName & ", " & rstView!Country
        rstView.MoveNext
    Loop

    ' Change underlying data.
    Set rstChange = dbsNorthwind.OpenRecordset("Employees")
    rstChange.AddNew
    rstChange!FirstName = "Nina"
    rstChange!LastName = "Roberts"
    rstChange!Country = "USA"
    rstChange.Update

    rstView.Requery
    Debug.Print "Requery after changing underlying data"
    Set rstView = qdfTemp.OpenRecordset
    Do While Not rstView.EOF
        Debug.Print "  " & rstView!FirstName & " " & _
            rstView!LastName & ", " & rstView!Country
        rstView.MoveNext
    Loop

    ' Restore original data because this is only a 
    ' demonstration.
    rstChange.Bookmark = rstChange.LastModified
    rstChange.Delete
    rstChange.Close

    rstView.Close
    dbsNorthwind.Close

End Sub

This example shows how the Requery method can be used to refresh a query after the query parameters have been changed.

Sub RequeryX2()

    Dim dbsNorthwind As Database
    Dim qdfTemp As QueryDef
    Dim prmCountry As Parameter
    Dim rstView As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set qdfTemp = dbsNorthwind.CreateQueryDef("", _
        "PARAMETERS ViewCountry Text; " & _
        "SELECT FirstName, LastName, Country FROM " & _
        "Employees WHERE Country = [ViewCountry] " & _
        "ORDER BY LastName")
    Set prmCountry = qdfTemp.Parameters!ViewCountry

    qdfTemp.Parameters!ViewCountry = "USA"
    Debug.Print "Data after initial query, " & _
        [ViewCountry] = USA"
    Set rstView = qdfTemp.OpenRecordset
    Do While Not rstView.EOF
        Debug.Print "  " & rstView!FirstName & " " & _
            rstView!LastName & ", " & rstView!Country
        rstView.MoveNext
    Loop

    ' Change query parameter.
    qdfTemp.Parameters!ViewCountry = "UK"
    ' QueryDef argument must be included so that the 
    ' resulting Recordset reflects the change in the query 
    ' parameter.
    rstView.Requery qdfTemp
    Debug.Print "Requery after changing parameter, " & _
        "[ViewCountry] = UK"
    Do While Not rstView.EOF
        Debug.Print "  " & rstView!FirstName & " " & _
            rstView!LastName & ", " & rstView!Country
        rstView.MoveNext
    Loop

    rstView.Close
    dbsNorthwind.Close

End Sub